SunSystems Journal
Another common requirement for integrating with SunSystems is to post a journal. This is a transaction that gets sent to Sun, usually as the final step of a process after all relevant approvals have been granted. If successful the relevant accounting entries are written to the SunSystems ledger and a journal number returned as part of the response. This journal number can then be written back to a suitable field on UniFi in order to link the UniFi transaction with the SunSystems ledger transaction.
The process is broadly the same as the query step described previously, however there are some additional considerations worth noting.
Template Script
After the security token has been obtained the next step would be to use a template script to construct the XML payload to send to SunSystems Connect. This is likely to be more complex that the query script as we need to pick up many more fields from the form, but the basic process is the same. When constructing the input there are a few things to think about:
Reading JSON dropdowns
The dropdowns created in the previous step will appear on the form as JSON data containing as many columns as you specified. So a selection including account code and description will look something like:
{\"AccountCode\":\"81009\",\"SupplierName\":\"FinanSys Solutions\"}
To extract the account code for use in the journal posting we can use the JSON.parse formula, so assuming the field ID is supplier the following formula can be used:
payload.supplier = JSON.parse(payload.supplier).AccountCode;
This will give the payload a supplier value of "81009" which is the value we need to send to Sun as an account code for the supplier line of the journal.
Take care as all these settings are case sensitive. UniFi fields ID's are all lower case, whereas Sun XML tags are mixed case with the first letter of each word capitalised.
Reformatting for SunSystems
SunSystems has some specific formats when it comes to dates and accounting periods. Dates must be sent in the format "ddMMyyyy" which is different to the standard JSON format of "yyyy-MM-dd". We can use the "substr" formula to convert as follows:
payload.invoice_date = payload.invoice_date.substr(8,2)+payload.invoice_date.substr(5,2)+payload.invoice_date.substr(0,4);
In this case dividing the original format into the day, month and year components and then reassembling into the correct format for Sun. Similar formulas can be used to populate the accounting period (Sun default format of "pppyyyy") and indeed anything else where Sun requires a specific format for the data it will accept.
Reading from a table.
Often a UniFi form will include a table, typically you will need to turn each line of that table into one or more journal lines in Sun. E.g. in a purchase invoice typically the gross total will credit the supplier account, but then the table will contain multiple expenditure lines each of which needs to be debited separately to the appropriate expenditure code. Firstly, if the table contains any JSON dropdowns we will need to convert these to the codes in the same way as the supplier account code above. We can use the forEach function in combination with an 'index' to step through and convert each line. So to get the AccountCode tag from each line of 'nominal' code selection in 'table_1' we can do the following:
Object.keys(payload.table_1).forEach(function(key,index) {
payload.table_1\[index].nominal = JSON.parse(payload.table_1\[index].nominal).AccountCode;
});
This then needs to be incorporated into the 'content' section by dropping the field ID in double-curly brackets, but we can use the {{#each }} keyword to indicate we want to step through each line of the table, as in the following example:
{{#each table_1}}
<Line>
<AccountCode>{{nominal}}</AccountCode>
...
</line>
{{/each}}
This will create a new 'line' of the XML for each line of the UniFi table.
You can step through the root nodes of the JSON input using the syntax {{#each .}}
Testing with a sample payload
When testing the data connector it is often useful to have a test payload that you can run in the editing screen before actually using in an app. To do this you can create a static 'payload' with example values, something like:
var payload = {
"business_unit": "PK1",
"innoice_number": "Test123".
...
}
You can then instruct the data connector to use the actual payload from the form when run from the app by using the following code with 'temp_input' being defined as the input variable in the first stage of the data connector:
if(Object.keys(temp_input).length>0) {
payload = JSON.parse(temp_input);
}
This means that if run as a test from the data connector builder the example payload will be used, but if run from the actual app then the actual app data entered will be used instead.
Full Template Script example
The following is an example journal posting template script for a purchase invoice incorporating all of the above.
Input (Javascript):
var payload = {
"token": tokenParser.Output ,
"business_unit": "PK1",
"supplier": "{\"AccountCode\":\"81001\",\"SupplierName\":\"Electrical Supplies Wholesaler\"}",
"invoice_number": "PI-001",
"invoice_date": "2022-08-02T00:00:00",
"credit_note": false,
"invoice_total_net": "100",
"invoice_total_tax": "20",
"invoice_total_gross": "120",
"currency_code": "GBP",
"tax_account": "94200",
"suspense_account": "999",
"invoice_journal_type": "PIINV",
"table_1": [
{
"line_description": "Line 1",
"nominal": "{\"AccountCode\":\"11040\",\"Description\":\"Annual Airtime Contract\"}",
"department": "{\"AnalysisCode\":\"11\",\"Name\":\"Publications\"}",
"tax_code": "{\"AnalysisCode\":\"V\",\"Name\":\"Standard Rate VAT\"}",
"quantity": 1.0,
"unit_price": 100.0,
"net_amount": 100.0,
"tax_amount": 20.0,
"gross_amount": 120.0
}
]
}
//substitute payload with actual input if called from app
if(Object.keys(temp_input).length>0) {
payload = JSON.parse(temp_input);
payload\["token"] = tokenParser.Output;
}
//Header and footer field processing
payload.supplier = JSON.parse(payload.supplier).AccountCode;
payload.invoice_date = payload.invoice_date.substr(8,2)+payload.invoice_date.substr(5,2)+payload.invoice_date.substr(0,4);
//table_1 processing
Object.keys(payload.table_1).forEach(function(key,index) {
payload.table_1\[index].nominal = JSON.parse(payload.table_1\[index].nominal).AccountCode;
payload.table_1\[index].department = JSON.parse(payload.table_1\[index].department).AnalysisCode;
payload.table_1\[index].tax_code = JSON.parse(payload.table_1[\index].tax_code).AnalysisCode;
});
return payload;
Content (Literal):
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://systemsunion.com/connect/webservices/">
<soapenv:Body>
<web:ComponentExecutorExecuteRequest>
<web:authentication>{{token}}</web:authentication>
<web:component>Journal</web:component>
<web:method>Import</web:method>
<web:payload><![CDATA[
<SSC>
<SunSystemsContext>
<BusinessUnit>{{business_unit}}</BusinessUnit>
<BudgetCode>A</BudgetCode>
</SunSystemsContext>
<MethodContext>
<LedgerPostingParameters>
<AllowBalTran>1</AllowBalTran>
<AllowPostToSuspended>N</AllowPostToSuspended>
<JournalType>PIINV</JournalType>
<LoadOnly>N</LoadOnly>
<PostProvisional>N</PostProvisional>
<PostToHold>N</PostToHold>
<PostingType>2</PostingType>
<ReportingAccount>999</ReportingAccount>
<ReportErrorsOnly>Y</ReportErrorsOnly>
<SuppressSubstitutedMessages>Y</SuppressSubstitutedMessages>
<SuspenseAccount>999</SuspenseAccount>
<TransactionAmountAccount>999</TransactionAmountAccount>
</LedgerPostingParameters>
</MethodContext>
<Payload>
<Ledger>
<Line>
<AccountCode>{{supplier}}</AccountCode>
<AnalysisCode1>#</AnalysisCode1>
<AnalysisCode2>#</AnalysisCode2>
<AnalysisCode3>#</AnalysisCode3>
<AnalysisCode4>#</AnalysisCode4>
<AnalysisCode6>#</AnalysisCode6>
<TransactionAmount>{{invoice_total_gross}}</TransactionAmount>
<CurrencyCode>{{currency_code}}</CurrencyCode>
<DebitCredit>C</DebitCredit>
<Description>Purchase Invoice</Description>
<TransactionReference>{{invoice_number}}</TransactionReference>
<TransactionDate>{{invoice_date}}</TransactionDate>
<DetailLad>
<GeneralDescription1></GeneralDescription1>
<GeneralDescription2></GeneralDescription2>
<GeneralDescription3></GeneralDescription3>
</DetailLad>
</Line>
<Line>
<AccountCode>{{tax_account}}</AccountCode>
<AnalysisCode1>#</AnalysisCode1>
<AnalysisCode2>#</AnalysisCode2>
<AnalysisCode3>#</AnalysisCode3>
<AnalysisCode4>#</AnalysisCode4>
<AnalysisCode6>#</AnalysisCode6>
<TransactionAmount>{{invoice_total_tax}}</TransactionAmount>
<CurrencyCode>{{currency_code}}</CurrencyCode>
<DebitCredit>D</DebitCredit>
<Description>Purchase Invoice</Description>
<TransactionReference>{{invoice_number}}</TransactionReference>
<TransactionDate>{{invoice_date}}</TransactionDate>
<DetailLad>
<GeneralDescription1></GeneralDescription1>
<GeneralDescription2></GeneralDescription2>
<GeneralDescription3></GeneralDescription3>
</DetailLad>
</Line>
{{#each table_1}}
<Line>
<AccountCode>{{nominal}}</AccountCode>
<AnalysisCode1>#</AnalysisCode1>
<AnalysisCode2>{{department}}</AnalysisCode2>
<AnalysisCode3>#</AnalysisCode3>
<AnalysisCode4>#</AnalysisCode4>
<AnalysisCode6>{{tax_code}}</AnalysisCode6>
<TransactionAmount>{{net_amount}}</TransactionAmount>
<CurrencyCode>{{../currency_code}}</CurrencyCode>
<DebitCredit>D</DebitCredit>
<Description>{{line_description}}</Description>
<TransactionReference>{{../invoice_number}}</TransactionReference>
<TransactionDate>{{../invoice_date}}</TransactionDate>
<DetailLad>
<GeneralDescription1></GeneralDescription1>
<GeneralDescription2></GeneralDescription2>
<GeneralDescription3></GeneralDescription3>
</DetailLad>
</Line>
{{/each}}
</Ledger>
</Payload>
</SSC>]]>
</web:payload>
</web:ComponentExecutorExecuteRequest>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Returning the response
With the query data connectors, the final response is to return the data in JSON format to be used in the form dropdown control. With journal postings we will want to vary the response depending on whether the journal is successful or not. The path to the success or failure is in the SSC / Payload / Ledger XML node and so the query expression '/SSC/Payload/Ledger[*]' can be used in the XML text parser stage.
You can then check the results and use it to determine which fields in the app should be updated. The following code example will output a journalNumber in the case of a successful posting, or a journalMessage in the case of an unsuccessful one.
var temp_result = JSON.parse(PostingParserJSON.Output).result;
var journalNumber = "";
var journalMessage = "";
var finalResult = {"success":false,"journalNumber":null,"journalMessage":null};
if(temp_result?.Ledger?.Line\[0]?.\["@status"] === "success") {
journalNumber=temp_result?.Ledger?.Line[0]?.JournalNumber;
finalResult.success = true;
finalResult.journalNumber = journalNumber;
finalResult.journalMessage = "";
}
else {
for (i=0; i<temp_result.Ledger.Line.length && !journalMessage; i++) {
JournalMessage = temp_result.Ledger.Line[i].Messages?.Message[0].UserText;
}
finalResult.journalNumber = "";
finalResult.journalMessage = journalMessage;
}
This return can then be used in a subsequent 'Set Value' step of the process to populate relevant fields using code such as:
return JournalPosting.Output.WorkflowOutput.journalNumber;
Depending on what values are returned, this can they drive the routing of subsequent stages of the process. E.g. if a journal number is returned then complete the process, if not route to someone in the finance department to review the posting error and reject or retry as appropriate.
Using a data connector in an app process
Once that data connector has been successfully tested you are ready to use it in an app. From the process tab of your app first ensure you have a GetCurrentRecord step before the data connector in the process steps. Then drag on 'Data Connector' as a step in the process, select the data connector you have created and enter a Javascript input of'return CurrentRecord.Result'. You can also set the mode to be either 'Fire and Forget' (don't wait for a response) or 'Blocking' (wait for a response before continuing) - use the second option if you are intending to do something with the response afterwards like write back the journal number.